
create FUNCTION fnTotalCarteraDocumentada (@IdDesarrollo varchar(3)
	, @Intercambio smallint)
RETURNS money
AS
BEGIN
   DECLARE @TotCartera money
   DECLARE @TotCarteraMens money
   DECLARE @TotCarteraEng1 money
   DECLARE @TotCarteraEng2 money
   
   If @Intercambio = 0
   BEGIN
	----  Mensualidades ------
   	set @TotCarteraMens = (Select Sum(DV.Mensualidad) as Total 
	  FROM DetVenta DV INNER JOIN Desarrollo D 
	  ON DV.CveLote = D.CveLote
	  WHERE D.CveDesarrollo = @IdDesarrollo
	  and (D.Intercambio <> 'I' or D.Intercambio is null)
	  AND D.StaVenta='C')
	
	if @TotCarteraMens IS NULL SET @TotCarteraMens = 0

   
   	--  	Enganches Ordinarios y pagos de contado ----
   	set @TotCarteraEng1 = (SELECT sum(Importe) as Total
   	   FROM 	Enganches E INNER JOIN Desarrollo D 
   	   ON 	E.CveLote = D.CveLote
	   WHERE D.CveDesarrollo = @IdDesarrollo  
   	   and D.StaVenta='C' AND E.nopago = 1 
	   and (D.Intercambio <> 'I' or D.Intercambio is null)
   	   and E.tipo in ('C','E'))

	if @TotCarteraEng1 IS NULL SET @TotCarteraEng1 = 0

   	set @TotCarteraEng2 = (SELECT SUM(Importe) as Total
   	   FROM 	Enganches E INNER JOIN Desarrollo D
	   ON E.CveLote = D.CveLote
   	   WHERE D.CveDesarrollo = @IdDesarrollo 
    	   and (D.Intercambio <> 'I' or D.Intercambio is null)
	   and D.StaVenta = 'C' and E.tipo = 'D')

	if @TotCarteraEng2 IS NULL SET @TotCarteraEng2 = 0
   end

   If @Intercambio > 0
   BEGIN
	----  Mensualidades ------
   	set @TotCarteraMens = (Select Sum(DV.Mensualidad) as Total 
	  FROM DetVenta DV INNER JOIN Desarrollo D 
	  ON DV.CveLote = D.CveLote
	  WHERE D.CveDesarrollo = @IdDesarrollo
	  and (D.Intercambio = 'I')
	  AND D.StaVenta='C')

	if @TotCarteraMens IS NULL SET @TotCarteraMens = 0
   
   	--  	Enganches Ordinarios y pagos de contado ----
   	set @TotCarteraEng1 = (SELECT sum(Importe) as Total
   	   FROM 	Enganches E INNER JOIN Desarrollo D 
   	   ON 	E.CveLote = D.CveLote
	   WHERE D.CveDesarrollo = @IdDesarrollo
   	   and D.StaVenta='C' AND E.nopago = 1
	   and (D.Intercambio = 'I')
           and E.tipo in ('C','E'))

	if @TotCarteraEng1 IS NULL SET @TotCarteraEng1 = 0

   	set @TotCarteraEng2 = (SELECT SUM(Importe) as Total
   	   FROM 	Enganches E INNER JOIN Desarrollo D
	   ON E.CveLote = D.CveLote
   	   WHERE D.CveDesarrollo = @IdDesarrollo
    	   and (D.Intercambio = 'I')
 	   and D.StaVenta = 'C' and E.tipo = 'D')
	
	if @TotCarteraEng2 IS NULL SET @TotCarteraEng2 = 0
	
   end

   SET @TotCartera = @TotCarteraMens + @TotCarteraEng1 + @TotCarteraEng2	
   	return (@TotCartera)
END
go


